const express = require('express');
const router = express.Router();
const mysql=require('mysql');
const querystring=require('querystring');
const multiparty = require('multiparty');
const util = require('util');
const fs=require('fs');

let connection=mysql.createPool({
  host     : 'rm-uf6hsi42u17lj6t8hmo.mysql.rds.aliyuncs.com',
  user     : 'root',
  password : 'Cjy199387',
  port: '3306',
  database:'demo'
});

function query(sql,callback,res){
  connection.getConnection(function(err,connection){
    if(err){
      console.log(err);
      // res.send('数据库连接错误');
    }else {
      connection.query(sql, function (err,rows) {
        callback(err,rows);
        connection.release();
      });
    }
  });
}

function error(err) {
  if (err) {console.log(err);return false;}
}

// 上传图片
let dstPath='';
router.post('/uploadimg',function (req, res, next) {
  let form = new multiparty.Form({uploadDir: './public/images'});
  //上传完成后处理
  form.parse(req, function(err, fields, files) {
    let filesTmp = JSON.stringify(files,null,2);
    if(err){
      console.log(err);
    } else {
      // console.log(filesTmp);
      let inputFile = files.user_img[0];
      let uploadedPath = inputFile.path;
      dstPath = './public/images/' + inputFile.originalFilename;
      //重命名为真实文件名
      fs.rename(uploadedPath, dstPath);
    }
    // res.writeHead(200, {'content-type': 'text/plain;charset=utf-8'});
    // res.write('received upload:\n\n');
    // res.end(util.inspect({fields: fields, files: filesTmp}));
    res.send(dstPath.substring(8));

  });
});

let sql=null;
// 获取列表
router.get('/', function(req, res, next) {
  sql='select * from student limit 20';
  query(sql,function (err,rows) {
    error(err);
    // console.log(JSON.stringify(rows));
    res.send(JSON.stringify(rows));
  },res);
});

// 新增
router.post("/add",function(req,res,next){
  let data=req.body;
  let name = data.name,
    age = data.age,
    sex=data.sex,
    tel=data.tel,
    score=data.score,
    user_img='http://localhost:8888'+dstPath.substring(8);
  if(dstPath===''){
    user_img='';
  }
  sql="insert into student(name,age,sex,tel,score,user_img) values('"+name+"','"+age+"','"+sex+"','"+tel+"','"+score+"','"+user_img+"')";
  // console.log(sql);
  query(sql,function(err,rows){
    if(err){
      res.send(err);
    }else {
      dstPath='';
      res.send(200);
    }
  });
});


// 删除用户
router.get("/del",function(req,res){
  let id = req.query.id;
  sql='DELETE FROM student where id in ('+id+')';
  query(sql,function(err,rows){
    if(err){
      res.send("删除失败"+err);
    }else {
      res.send(rows);
    }
  });
});


// 修改
router.post("/modify",function(req,res,next){
  let data=req.body.data;
  // console.log(req.body);
  let id = req.body.id,
    name = data.name,
    age = data.age,
    sex=data.sex,
    tel=data.tel,
    score=data.score,
    user_img='http://localhost:8888'+dstPath.substring(8);
  if(dstPath===''){
    sql="update student set name='"+name+"',age='"+age+"',sex='"+sex+"',tel='"+tel+"',score='"+score+"' where id = " +id;
  }else {
    sql="update student(name,age,sex,tel,score,user_img) values('"+name+"','"+age+"','"+sex+"','"+tel+"','"+score+"','"+user_img+"')where id = " + id;
  }
  // console.log(sql);
  query(sql,function(err,rows){
    if(err){
      res.send("修改失败 " + err);
    }else {
      res.send(rows);
    }
  });
});


/**
 * 查询
 */
router.post("/search",function(req,res,next){
  let name = req.body.s_name;
  let age = req.body.s_age;
  let sql = "select * from user";
  if(name){
    sql += " where name = '"+ name +"'";
  }
  //if(age){
  //    sql += " and age = '" + age + "'";
  //}

  sql.replace("and","where");
  query(sql,function(err,rows){
    if(err){
      res.send("查询失败: "+err);
    }else{
      res.render("users",{title:"用户列表",datas:rows,s_name:name,s_age:age});
    }
  });
});

module.exports = router;
